package com.hnxxxy.statistics.mapper;

import com.hnxxxy.statistics.vo.JfTotalVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import java.util.List;

/**
 *@author:zqh
 *@date：2020年6月23日20:12:08
 * 积分排行总表
 **/

@Mapper
public interface TotalMapper {
    /**
     * 积分排行
     *
     * @param
     * @return 返回全校积分排行查询
     */
    @Select("SELECT a.name,a.sex,a.nativeplace,a.major,a.college,b.score, " +
            "(SELECT COUNT(score) FROM jf_total c WHERE c.score > b.score)+1 AS rank " +
            "FROM cst_user a,jf_total b " +
            "WHERE a.id = b.user_id and 1=1 " +
            "ORDER BY b.score DESC")
    List<JfTotalVo> findTotalRank();


    @Select({"<script>",
            "SELECT a.name,a.sex,a.nativeplace,a.major,a.college,b.score ",
            "(SELECT COUNT(score) FROM jf_total c WHERE c.score > b.score)+1 AS rank ",
            "FROM cst_user a,jf_total b " ,
            "<where> ",
            "<if test='menuname!=null'>",
            "and menuname like concat('%',#{menuname},'%')",
            "</if>",
            "<if test='type!=null'>",
            "and type like concat(#{type})",
            "</if>",
            "</where>",
            "ORDER BY b.score DESC",
            "</script>"})
    List<JfTotalVo> findAllTotalRank();



    @Select("SELECT a.name,a.sex,a.nativeplace,a.major,a.college,b.score, " +
            "(SELECT COUNT(score) FROM jf_total c WHERE c.score > b.score)+1 AS rank " +
            "FROM cst_user a,jf_total b " +
            "WHERE a.id = b.user_id and 1=1 " +
            "ORDER BY b.score DESC")
    List<JfTotalVo> findALLTotalRank();

    /**
     * 积分排行
     *
     * @param
     * @return 返回各学院积分排行查询
     */
    @Select("SELECT a.name,a.sex,a.nativeplace,a.major,a.college,b.score,\n" +
            "(SELECT COUNT(score) FROM jf_total c WHERE c.score > b.score)+1 AS rank\n" +
            "FROM cst_user a,jf_total b\n" +
            "WHERE a.id = b.user_id and a.college='电子信息学院' and 1=1\n" +
            "ORDER BY b.score DESC")
    List<JfTotalVo> findCollegeRank();
}
